#!/usr/bin/env bash
cd `dirname $0`
source ../../../config.sh
source ../../basic/app/app_table_data_source.sh
exec_dir course_statistics_info

HIVE_DB=app
HIVE_TABLE=course_statistics_info
TARGET_TABLE=course_statistics_info
MODEL_DB=major_course_record,semester_report,course_implement,student_score_record

function create_table(){
 hadoop fs -rm -r ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} || :
    hive -e "DROP TABLE IF EXISTS ${HIVE_DB}.${HIVE_TABLE};"
    hive -e "CREATE EXTERNAL TABLE IF NOT EXISTS ${HIVE_DB}.${HIVE_TABLE}(
	 semester_year  String comment'YYYY-YYYY',
	 semester String comment'学期  1第一学期   2 第二学期',
	 curse_num   String comment'课程总数',
	 core_num    int   comment'专业核心课数',
	 open_num 		int comment'精品在线开放课程数',
	 cooperate_num   int comment'校企合作联合开发课程数',
	 share_num    int comment'精品资源共享课数',
	 activity   FLOAT comment'课堂活跃度',
	 avg_score_rate FLOAT comment'平均成绩增长率',
	 avg_score   FLOAT comment'卷面平均成绩',
	 excellent_rate  FLOAT comment'卷面成绩优秀率',
	 teaching_various   FLOAT comment'教学方法多样性',
   create_time  String  comment'创建时间'
   )COMMENT '课程概况统计'
    LOCATION '${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}'"
    fn_log "创建表——课程概况统计：${HIVE_DB}.${HIVE_TABLE}"
}
function import_table() {
	create_table
    hive -e "
         INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		 select
		 '${SEMESTER_YEAR}' as semester_year,
		 '${SEMESTER}' as semester,
		 a.course_num as curse_num,
		 a.core_num as core_num,
		 a.open_num as open_num,
		 b.cooperate_num as cooperate_num,
		 b.share_num as share_num,
		 c.activity as activity,
		 d.avg_score_rate as avg_score_rate,
		 e.avg_score as avg_score,
		f.excellent_rate as excellent_rate,
		 c.teaching_various as teaching_various,
		 FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
		 from
		 (
		 select 
		 count(case when a.category!='99' then a.course_code end) as course_num,
		 count(case when a.is_core_course='1' then a.course_code end) as core_num,
		 count(1) as open_num
		 from
		 model.major_course_record a
		 where a.semester_year='${SEMESTER_YEAR}'and a.semester='${SEMESTER}'
		 )a,
		 (
		select
		 COALESCE(cast(sum(b.cooperate_num) as int),0) as cooperate_num,
		 COALESCE(cast(sum(b.share_num) as int),0) as share_num
		 from model.semester_report b
		 where b.semester_year='${SEMESTER_YEAR}' and b.semester='${SEMESTER}'
		 )b,
		 (
		 select
		 nvl(sum(cast((c.work_correction_rate+c.sign_rate+c.questions_rate+c.forum_posting_rate)*0.25 as decimal(4,2))),0) as activity,
		 nvl(sum(cast((c.work_correction_rate+c.test_num+c.online_answer_num+c.online_discuss_num )/4 as decimal(4,2))),0) as teaching_various
		 from
		 (
		 select
		 cast(c.work_correction_rate as int) as work_correction_rate,
		 cast(c.sign_rate as int) as sign_rate,
		 cast(c.questions_rate as int) as questions_rate,
		 cast(c.forum_posting_rate as int) as forum_posting_rate,
		 cast(c.test_num as int) as test_num,
		 cast(c.online_answer_num as int) as online_answer_num,
		 cast(c.online_discuss_num as int) as online_discuss_num
		 from
		 model.course_implement c
		 where c.semester_year ='${SEMESTER_YEAR}' and c.semester='${SEMESTER}'
		)c)c,
		 (
				select

			nvl(concat(cast(((x.score1-y.score2)/y.score2)*100 as int),'%'),0) as avg_score_rate
				from
				(
				select
				sum(x.score)/count(x.code) as score1
				from
				model.student_score_record x
				where x.semester_year='${SEMESTER_YEAR}' and x.semester='${SEMESTER}'
				)x,
				(
				select
				sum(y.score)/count(y.code) as score2
				from
				model.student_score_record y
				where y.semester_year='${LAST_SEMESTER_YEARS}' and y.semester='${LAST_SEMESTERS}'
				)y
		 )d,
		 (
		 select

		COALESCE(avg(e.score),0) as avg_score
		 from
		 model.student_score_record e
		 where e.semester_year='${SEMESTER_YEAR}' and e.semester ='${SEMESTER}'
		 )e,
		  (
		 select
		 nvl(concat(coalesce(cast(sum(f.score)/count(f.code) as decimal(4,2)),0),'%'),0) as excellent_rate
		 from
		 model.student_score_record f
		 where f.semester_year='${SEMESTER_YEAR}' and f.semester ='${SEMESTER}' and cast(f.score as int)>90
		 )f
		 "
		     fn_log "导入数据 —— 课程概况统计：${HIVE_DB}.${HIVE_TABLE}"
			 export_table
	 }
function export_table() {
     clear_mysql_data "delete from ${TARGET_TABLE} where semester_year='${SEMESTER_YEAR}' and semester='${SEMESTER}';"
    sqoop export --connect ${MYSQL_URL} --username ${MYSQL_USERNAME} --password ${MYSQL_PASSWORD} \
    --table ${TARGET_TABLE} --export-dir  ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} \
    --input-fields-terminated-by '\0001' --input-null-string '\\N' --input-null-non-string '\\N' \
    --null-string '\\N' --null-non-string '\\N' \
    --columns 'semester_year,semester,curse_num,core_num,open_num,cooperate_num,share_num,activity,avg_score_rate,
	avg_score,excellent_rate,teaching_various,create_time'
    fn_log "导出数据--课程概况统计:${HIVE_DB}.${TARGET_TABLE}"
}

if [ $# == 1 ]
        then
        cur_semester_year
        data_cleaning
        finish
    else
        cur_semester_year
        finish
    fi